﻿using System;
using System.Collections.Generic;
using System.Data.SQLite;
using System.Linq;

namespace Data {
    public class DAO : DatabaseProvider {
        #region Get

        /// <summary>
        /// Get Relate Words
        /// </summary>
        public string GetRelateWord(int langId, string word) {
            var paras = new[]
                            {
                                new SQLiteParameter("@langId", langId),
                                new SQLiteParameter("@word", word)
                            };
            return Convert.ToString
                (SqliteExecuteScalar(
                    @"SELECT key FROM Word WHERE lang_id = @langId
                                        AND commonvalue_id = 
                                        (SELECT commonvalue_id FROM Word WHERE key = @word)
                                        AND enable=1",
                    paras));
        }

        public List<string> GetWords(int langId) {
            if (langId != 0) {
                var paras = new[] {new SQLiteParameter("@langId", langId)};
                List<object> dataReader = SqliteDataReader(@"SELECT key FROM Word WHERE lang_id=@langId and enable=1",
                                                           paras);
                return dataReader.Select(data => data.ToString()).ToList();
            }
            else {
                List<object> dataReader = SqliteDataReader(@"SELECT key FROM Word WHERE enable=1", null);
                return dataReader.Select(data => data.ToString()).ToList();
            }
        }

        public List<string> GetAllLanguages() {
            List<object> dataReader = SqliteDataReader(@"SELECT name FROM Language", null);
            return dataReader.Select(data => data.ToString()).ToList();
        }

        public string GetCommonValueOf(string word) {
            var paras = new[] {new SQLiteParameter("@word", word)};
            return
                Convert.ToString(
                    SqliteExecuteScalar(
                        @"SELECT value FROM CommonValue WHERE id=
                        (SELECT commonvalue_id FROM Word WHERE key=@word)",
                        paras));
        }

        public string GetValueOf(string word) {
            var paras = new[] {new SQLiteParameter("@word", word)};
            return Convert.ToString(SqliteExecuteScalar(@"SELECT value FROM Word WHERE key = @word", paras));
        }

        public List<string> GetRelateWords(string word) {
            var paras = new[] {new SQLiteParameter("@word", word)};
            string eng = GetRelateWord(word, 1);
            string rus = GetRelateWord(word, 2);
            string vi = GetRelateWord(word, 3);
            return new List<string> {eng, rus, vi};
        }

        public string GetRelateWord(string word, int langId) {
            var paras = new[]
                            {
                                new SQLiteParameter("@word", word),
                                new SQLiteParameter("@langId", langId)
                            };
            return
                Convert.ToString(
                    SqliteExecuteScalar(
                        @"SELECT key FROM Word WHERE lang_id = @langId and commonvalue_id = (SELECT commonvalue_id FROM Word WHERE key = @word) and enable=1",
                        paras));
        }

        public string GetLangOf(string word) {
            var paras = new[] {new SQLiteParameter("@word", word)};
            return Convert.ToString(SqliteExecuteScalar(@"SELECT type FROM Dictionary WHERE key=@word", paras));
        }

        #endregion

        #region UPDATE

        public bool UpdateValue(string oldWord, string newWord, string value, string commonValue) {
            if (oldWord == null || value == null)
                return false;
            var paras = new[]
                            {
                                new SQLiteParameter("@word", oldWord),
                                new SQLiteParameter("@value", value),
                                new SQLiteParameter("@commonValue", commonValue)
                            };
            SqliteExecuteNonQuery(@"UPDATE Word SET value = @value WHERE key = @word", paras);
            if (commonValue != null)
                SqliteExecuteNonQuery(
                    @"UPDATE CommonValue SET value=@commonValue WHERE id = 
                     (SELECT commonvalue_id FROM Word WHERE key = @word)",
                    paras);
            // Change word
            if (newWord != null)
                ChangeWord(oldWord, newWord);
            return true;
        }

        public int GetCommonValueId(string word) {
            var paras = new[] {new SQLiteParameter("@word", word)};
            return Convert.ToInt32(SqliteExecuteScalar("SELECT commonvalue_id FROM Word where key = @word", paras));
        }

        public void DisableWord(string word) {
            var paras = new[]
                            {
                                new SQLiteParameter("@word", word)
                            };
            SqliteExecuteNonQuery("UPDATE Word SET enable=0 WHERE key=@word", paras);
        }

        #endregion

        #region INSERT

        /// <summary>
        /// Return id of commonvalue a word to dictionary
        /// </summary>
        /// <param name="word"></param>
        /// <param name="commonValue"></param>
        /// <param name="comValueId">this can not be used if commonValue is not null</param>
        /// <param name="specValue"></param>
        /// <param name="langId">Id of language</param>
        public int InsertWord(string word, string commonValue, int comValueId, string specValue, int langId) {
            int commonValueId = 0;
            var paras = new[]
                            {
                                new SQLiteParameter("@word", word),
                                new SQLiteParameter("@commonValue", commonValue),
                                new SQLiteParameter("@specValue", specValue),
                                new SQLiteParameter("@lang_id", langId),
                                new SQLiteParameter("@commonValueId", commonValueId)
                            };
            if (commonValue != null)
                commonValueId =
                    Convert.ToInt32(
                        SqliteExecuteScalar(
                            @"INSERT into CommonValue (value) values (@commonValue);
                                                                      SELECT last_INSERT_rowid();",
                            paras));
            else {
                commonValueId = comValueId;
            }
            paras.SetValue(new SQLiteParameter("@commonValueId", commonValueId), 4);
            SqliteExecuteNonQuery(
                @"INSERT into Word (key,value,commonvalue_id,lang_id) values (@word,@specValue,@commonValueId,@lang_id)",
                paras);
            return commonValueId;
        }

        #endregion

        #region Helper

        // return -1 if there is none of this word in the dictionary
        // return 0 if it's OK
        // return 1 if there is a word already in dictionary
        public int ChangeWord(string oldWord, string newWord) {
            // check words
            if (!CheckWord(oldWord))
                return -1;
            if (CheckWord(newWord))
                return 1;

            // UPDATE the word.
            var paras = new[]
                            {
                                new SQLiteParameter("@oldWord", oldWord),
                                new SQLiteParameter("@newWord", newWord)
                            };
            SqliteExecuteNonQuery(@"UPDATE Word SET key=@newWord WHERE key=@oldWord", paras);
            return 0;
        }

        public bool CheckWord(string word) {
            var paras = new[] {new SQLiteParameter("@word", word)};
            return Convert.ToBoolean(SqliteExecuteScalar("SELECT id FROM Word WHERE key=@word", paras));
        }

        #endregion
    }
}